Title: SQL W3S (By-BKK) / 040 Null
Tags: Obsidian_to_Anki

Q: What is the purpose of ISNULL(), IFNULL(), NVL(), and COALESCE() functions in SQL?
A: These functions are used to handle NULL values and replace them with alternate values in SQL queries

Q: Which MySQL function replaces NULL with an alternative value?
A: IFNULL()

Q: True or False: IFNULL() is used in MySQL to replace NULL values
A: True

Q: Write a MySQL query to replace NULL in UnitsOnOrder with 0
A: SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0)) FROM Products

Q: What is the function in SQL Server to replace NULL values?
A: ISNULL()

Q: True or False: ISNULL() is only available in SQL Server
A: True

Q: Write a SQL Server query to replace NULL UnitsOnOrder with 0
A: SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0)) FROM Products

Q: How does MS Access check if a value is NULL?
A: Using the IsNull() function

Q: True or False: IsNull() in MS Access returns TRUE for null values
A: True

Q: Write a MS Access query replacing NULL UnitsOnOrder with 0
A: SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder)) FROM Products

Q: What is the Oracle function to handle NULL replacement?
A: NVL()

Q: True or False: NVL() is used in Oracle to replace a NULL value
A: True

Q: Write an Oracle query to replace NULL UnitsOnOrder with 0
A: SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0)) FROM Products

Q: What is the role of COALESCE() function in SQL?
A: COALESCE() returns the first non-null value from a list of arguments

Q: Give an example of using COALESCE() to replace NULL UnitsOnOrder with 0
A: SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0)) FROM Products

Q: True or False: COALESCE() works in MySQL, SQL Server, and Oracle
A: True

Q: Reverse: ISNULL() replaces NULL values in SQL Server
A: Which function in SQL Server replaces NULL values?

Q: Reverse: COALESCE() returns the first non-null value
A: What does the COALESCE() function do in SQL?

Q: Reverse: IFNULL() is used in MySQL
A: Which database uses the IFNULL() function for NULL handling?

Q: Reverse: NVL() handles NULL replacement in Oracle
A: Which function in Oracle is used to replace NULL values?